package com.bittech.everything.core.dao.impl;

import com.bittech.everything.core.model.Condition;
import com.bittech.everything.core.model.FileType;
import com.bittech.everything.core.model.Thing;
import com.bittech.everything.core.dao.FileIndexDao;

import javax.sql.DataSource;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Author: secondriver
 * Created: 2018/9/26
 */
public class DatabaseFileIndexDao implements FileIndexDao {
    
    private final DataSource dataSource;
    
    public DatabaseFileIndexDao(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    @Override
    public void insert(Thing thing) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            String sql = "insert into file_index (name, file_type, path, depth) values (?,?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1, thing.getName());
            statement.setString(2, thing.getFileType().name());
            statement.setString(3, thing.getPath());
            statement.setInt(4, thing.getDepth());
            statement.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackResource(connection);
        } finally {
            releaseResource(null, statement, connection);
        }
    }
    
    @Override
    public void delete(Thing thing) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            if (new File(thing.getPath()).isFile()) {
                String sql = "delete from file_index where path= ? ";
                statement = connection.prepareStatement(sql);
                statement.setString(1, thing.getPath());
            } else {
                String sql = "delete  from file_index where  path like '%" + thing.getPath() + "%'";
                statement = connection.prepareStatement(sql);
            }
            statement.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackResource(connection);
        } finally {
            releaseResource(null, statement, connection);
        }
    }
    
    private void releaseResource(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    private void rollbackResource(Connection connection) {
        if (connection != null) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }
    
    @Override
    public List<Thing> query(Condition condition) {
        List<Thing> things = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = dataSource.getConnection();
            StringBuilder sql = new StringBuilder("select name,file_type,path,depth from file_index")
                    .append(" where name like ? ");
            if (condition.getFileType() != null) {
                sql.append(" and file_type = ? ");
            }
            sql.append(" order by depth ")
                    .append(condition.isOrderByAsc() ? "asc" : "desc").append(" limit  ").append(condition.getLimit());
            
            statement = connection.prepareStatement(sql.toString());
            //如果name列有索引进行优化，则检索选择方案二
            //方案一：前后模糊匹配
            //statement.setString(1, "%"+condition.getName() + "%");
            //方案二：后模糊匹配
            statement.setString(1, condition.getName() + "%");
            if (condition.getFileType() != null) {
                statement.setString(2, condition.getFileType().toUpperCase());
            }
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Thing thing = new Thing();
                thing.setName(resultSet.getString("name"));
                thing.setFileType(FileType.lookupByName(
                        resultSet.getString("file_type")
                ));
                thing.setPath(resultSet.getString("path"));
                thing.setDepth(resultSet.getInt("depth"));
                things.add(thing);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            releaseResource(resultSet, statement, connection);
        }
        return things;
    }
}